﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XRepair.Models;
using RepositoryUtil.SQL;
using XRepair.Conventions.Enum;
using System.Data;
using System.Data.SqlClient;

namespace MvcXRepairCustomer.Repository.SQL
{
    public class ModelDAO : BaseDAO
    {
        public IEnumerable<Model> GetAllActiveModels()
        {
            string spName = "GetAllActiveModel";

            DataTable resultTab = null;
            List<Model> modelList = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                resultTab = command.ExecuteDataTable();
            }
            if (resultTab != null && resultTab.Rows.Count > 0)
            {
                modelList = this.ConvertDataTableToModelList(resultTab);
            }

            return modelList;
        }

        public bool DeactivateModelByID(int modelId)
        {
            string spName = "DeactiveModelByID";

            object Rowcount = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", modelId);
                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;

            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool UpdateModel(Model model)
        {
            string spName = "UpdateModel";
            object Rowcount = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", model.ID);
                command.AddInParameter("BrandID", model.BrandID);
                command.AddInParameter("ModelName", model.ModelName);
                command.AddInParameter("Version", model.Version);
                command.AddInParameter("Manufacturer", model.Manufacturer);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;

            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool AddNewModel(Model model)
        {
            string spName = "AddNewModel";
            object newIdentity = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("BrandID", model.BrandID);
                command.AddInParameter("ModelName", model.ModelName);
                command.AddInParameter("Version", model.Version);
                command.AddInParameter("Manufacturer", model.Manufacturer);
                command.AddInParameter("CreatedAt", model.CreatedAt);
                command.AddInParameter("CreatedBy", model.CreatedBy);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }

        private List<Model> ConvertDataTableToModelList(DataTable data)
        {
            List<Model> models = null;

            if (data != null)
            {
                models = new List<Model>();

                foreach (DataRow dr in data.Rows)
                {
                    var serviceModel = new Model();

                    serviceModel.ID = SqlUtil<int>.GetValue(dr, "ID");
                    serviceModel.BrandID = SqlUtil<int>.GetValue(dr, "BrandID");
                    serviceModel.ModelName = SqlUtil<string>.GetValue(dr, "ModelName");
                    serviceModel.Version = SqlUtil<string>.GetValue(dr, "Version");
                    serviceModel.Manufacturer = SqlUtil<string>.GetValue(dr, "Manufacturer");
                    serviceModel.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
                    serviceModel.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    serviceModel.CreatedBy = SqlUtil<String>.GetValue(dr, "CreatedBy");
                    serviceModel.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
                    serviceModel.LastUpdatedBy = SqlUtil<String>.GetValue(dr, "LastUpdatedBy");

                    models.Add(serviceModel);
                }
            }

            return models;
        }

    }
}
